﻿namespace QMVnSoft.DAL
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.IO;
    using System.Collections.Generic;
    using System.Windows.Forms;
    using QMVnSoft.DAL.Util;    

    public static class AppDataAccess
    {
        public static string Version = "2.0.0";
        private static bool ib_Connected = false;
        private static string is_ConnectionString = "";
        private static SqlConnection oAppConnection = null;

        public static string Backup(string ps_DatabaseName, string ps_FolderName)
        {
            string str;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_DataName", ps_DatabaseName), new SqlParameter("p_BackupPath", ps_FolderName) };
            values[1].Direction = ParameterDirection.InputOutput;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 0xfe;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "BackupDatabase";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                str = values[1].Value.ToString();
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return str;
        }

        public static DataTable CheckInUsed(string ps_FieldName, object po_KeyCode)
        {
            DataTable table2;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Field", ps_FieldName), new SqlParameter("p_Deleted_Value", po_KeyCode) };
            SqlConnection eZConnection = AppConnection;
            DataTable dataTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Check_Code_Deleted";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                CloseConnection(eZConnection);
                dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns["Ma_File"] };
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return table2;
        }

        public static bool CheckUserRights(string ps_UserName, string ps_FuncType, long pi_FuncID, int pi_Number)
        {
            bool flag;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", ps_UserName), new SqlParameter("p_FuncType", ps_FuncType), new SqlParameter("p_FuncID", pi_FuncID), new SqlParameter("p_RightNo", pi_Number), new SqlParameter("p_Access", null) };
            values[4].Direction = ParameterDirection.Output;
            values[4].SqlDbType = SqlDbType.Bit;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Check_Right_ID";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                flag = !((bool) values[4].Value);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return flag;
        }

        public static bool CheckUserRights(string ps_UserName, string ps_FuncType, string ps_Code, int pi_Number)
        {
            bool flag;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", ps_UserName), new SqlParameter("p_FuncType", ps_FuncType), new SqlParameter("p_Code", ps_Code), new SqlParameter("p_RightNo", pi_Number), new SqlParameter("p_Access", null) };
            values[4].Direction = ParameterDirection.Output;
            values[4].SqlDbType = SqlDbType.Bit;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Check_Right_Code";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                flag = !((bool) values[4].Value);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return flag;
        }

        public static void CloseConnection(SqlConnection po_Conn)
        {
            if ((po_Conn != null) && (po_Conn.State != ConnectionState.Closed))
            {
                try
                {
                    po_Conn.Close();
                }
                catch (Exception exception)
                {
                    throw new NotSupportedException("Cannot close SIM connection!", exception);
                }
            }
        }

        public static string Cong_So_Tt(string ps_Subsidiary)
        {
            string str;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Dvcs", ps_Subsidiary), new SqlParameter("p_Stt", null) };
            values[1].Direction = ParameterDirection.Output;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 20;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Cong_So_Tt";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                str = (string) values[1].Value;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return str;
        }

        private static SqlConnection CreateConnection()
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                CheckConnected = true;
                return connection;
            }
            catch (Exception)
            {
                CheckConnected = false;
            }
            return null;
        }

        public static bool Delete(string ps_TableName, object po_KeyCode)
        {
            bool flag;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Stt", po_KeyCode), new SqlParameter() };
            values[1].Direction = ParameterDirection.ReturnValue;
            values[1].SqlDbType = SqlDbType.Int;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = ps_TableName + "_Delete";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                flag = ((int) values[1].Value) == 1;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return flag;
        }

        public static object GetBuffer(string ps_VarName)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_ID_String", ps_VarName), new SqlParameter("p_Var_Value", null) };
            values[1].Direction = ParameterDirection.Output;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 0xfe;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Buffer_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return values[1].Value;
        }

        public static int GetFreeFields(string ps_TableName)
        {
            int num;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_File", ps_TableName), new SqlParameter("p_FreeFields", SqlDbType.BigInt) };
            values[1].Direction = ParameterDirection.Output;
            values[1].SqlDbType = SqlDbType.Int;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmFile_FreeFields_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                num = (int) values[1].Value;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return num;
        }

        public static object GetMemvar(int pi_English, string ps_VarName)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_English", pi_English), new SqlParameter("p_Var_Name", ps_VarName), new SqlParameter("p_Var_Value", null) };
            values[2].Direction = ParameterDirection.Output;
            values[2].SqlDbType = SqlDbType.NVarChar;
            values[2].Size = 0xfe;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Memvar_One_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return values[2].Value;
        }

        public static Bitmap GetPicture(string ps_TableName, string ps_KeyFieldName, object po_KeyData, string ps_FieldPictName, string ps_FieldPictData)
        {
            Bitmap bitmap2;
            string str = "SELECT " + ps_FieldPictName + ", " + ps_FieldPictData + " FROM " + ps_TableName + " WHERE " + ps_KeyFieldName + " = @p_KeyCode";
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_KeyCode", po_KeyData) };
            SqlConnection eZConnection = AppConnection;
            DataTable dataTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = str;
                command.CommandType = CommandType.Text;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                CloseConnection(eZConnection);
                if (((dataTable.Rows.Count == 0) || dataTable.Rows[0][ps_FieldPictData].ToString().Equals(string.Empty)) || dataTable.Rows[0][ps_FieldPictName].ToString().Equals(string.Empty))
                {
                    return null;
                }
                byte[] buffer = (byte[]) dataTable.Rows[0][ps_FieldPictData];
                MemoryStream stream = new MemoryStream();
                stream.Write(buffer, 0, buffer.Length);
                Bitmap bitmap = new Bitmap(stream);
                bitmap2 = bitmap;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return bitmap2;
        }

        public static object GetUservar(string ps_UserName, string ps_VarName)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", ps_UserName), new SqlParameter("p_Var_Name", ps_VarName), new SqlParameter("p_Var_Value", null) };
            values[2].Direction = ParameterDirection.Output;
            values[2].SqlDbType = SqlDbType.NVarChar;
            values[2].Size = 0xfe;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "UserVars_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return values[2].Value;
        }

        public static void MergeCode(string ps_FieldName, object po_OldCode, object po_NewCode)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Field", ps_FieldName), new SqlParameter("p_Old_Value", po_OldCode), new SqlParameter("p_New_Value", po_NewCode) };
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Change_Code";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void Parameter_Search_Select(string ps_File_Code, ref string ps_FieldList, ref string ps_FieldName, ref string ps_FieldOrder, ref string ps_FilterFieldList, ref bool pb_FilterType, ref bool pb_MatchCase, ref int pi_StartPos, ref int pi_MaxRows, ref bool pb_EachWord)
        {
            SqlParameter[] values = new SqlParameter[10];
            values[0] = new SqlParameter("p_Ma_File", ps_File_Code);
            values[1] = new SqlParameter("p_FieldList", ps_FieldList);
            values[1].Direction = ParameterDirection.InputOutput;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 0x80;
            values[2] = new SqlParameter("p_FieldName", ps_FieldName);
            values[2].Direction = ParameterDirection.InputOutput;
            values[2].SqlDbType = SqlDbType.NVarChar;
            values[2].Size = 0x20;
            values[3] = new SqlParameter("p_FieldOrder", ps_FieldOrder);
            values[3].Direction = ParameterDirection.InputOutput;
            values[3].SqlDbType = SqlDbType.NVarChar;
            values[3].Size = 0x80;
            values[4] = new SqlParameter("p_FilterFieldList", ps_FilterFieldList);
            values[4].Direction = ParameterDirection.InputOutput;
            values[4].SqlDbType = SqlDbType.NVarChar;
            values[4].Size = 0x80;
            values[5] = new SqlParameter("p_FilterType", (bool) pb_FilterType);
            values[5].Direction = ParameterDirection.InputOutput;
            values[5].SqlDbType = SqlDbType.Bit;
            values[5].Size = 1;
            values[6] = new SqlParameter("p_MatchCase", (bool) pb_MatchCase);
            values[6].Direction = ParameterDirection.InputOutput;
            values[6].SqlDbType = SqlDbType.Bit;
            values[6].Size = 1;
            values[7] = new SqlParameter("p_StartPos", (int) pi_StartPos);
            values[7].Direction = ParameterDirection.InputOutput;
            values[7].SqlDbType = SqlDbType.Int;
            values[8] = new SqlParameter("p_MaxRows", (int) pi_MaxRows);
            values[8].Direction = ParameterDirection.InputOutput;
            values[8].SqlDbType = SqlDbType.Int;
            values[9] = new SqlParameter("p_EachWord", (bool) pb_EachWord);
            values[9].Direction = ParameterDirection.InputOutput;
            values[9].SqlDbType = SqlDbType.Bit;
            values[9].Size = 1;
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmFile_Filter_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                ps_FieldList = ((string) values[1].Value).Trim();
                ps_FieldName = ((string) values[2].Value).Trim();
                ps_FieldOrder = ((string) values[3].Value).Trim();
                ps_FilterFieldList = ((string) values[4].Value).Trim();
                pb_FilterType = (bool) values[5].Value;
                pb_MatchCase = (bool) values[6].Value;
                pi_StartPos = (int) values[7].Value;
                pi_MaxRows = (int) values[8].Value;
                pb_EachWord = (bool) values[9].Value;
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void Restore()
        {
        }

        public static bool RunSQLCommand(string ps_SQLCommand)
        {
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = ps_SQLCommand;
                command.CommandType = CommandType.Text;
                command.Connection = eZConnection;
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool RunSQLCommand(string ps_SQLCommand, string[] paramsName, object[] paramsValue)
        {
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            List<SqlParameter> values = new List<SqlParameter>();
            for (int i =0; i<paramsName.Length; i++)
            {
                values.Add(new SqlParameter(paramsName[i], paramsValue[i]));
            }            
            try
            {
                command.CommandText = ps_SQLCommand;
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(values.ToArray());
                command.Connection = eZConnection;
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool RunStore(string ps_SQLCommand, string[] paramsName, object[] paramsValue)
        {
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            List<SqlParameter> values = new List<SqlParameter>();
            for (int i = 0; i < paramsName.Length; i++)
            {
                values.Add(new SqlParameter(paramsName[i], paramsValue[i]));
            }
            try
            {
                command.CommandText = ps_SQLCommand;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(values.ToArray());
                command.Connection = eZConnection;
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
                return true;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
        }

        public static DataRow SelectDataRow(bool ib_EmptyIsValid, string ps_TableName, string ps_FieldName, string ps_FieldValue, string ps_FirstFilter)
        {
            DataRow row;
            SqlConnection eZConnection = AppConnection;
            DataTable dataTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                string str;
                if (ib_EmptyIsValid)
                {
                    if ((ps_FirstFilter != null) && !ps_FirstFilter.Equals(string.Empty))
                    {
                        str = "SELECT TOP 1 * FROM " + ps_TableName + " WHERE (" + ps_FieldName + " = N'" + ps_FieldValue.Replace("'", "''") + "' OR " + ps_FieldName + " LIKE N'" + ps_FieldValue.Replace("'", "''") + "%') AND (" + ps_FirstFilter + ")";
                    }
                    else
                    {
                        str = "SELECT TOP 1 * FROM " + ps_TableName + " WHERE (" + ps_FieldName + " = N'" + ps_FieldValue.Replace("'", "''") + "' OR " + ps_FieldName + " LIKE N'" + ps_FieldValue.Replace("'", "''") + "%')";
                    }
                }
                else if ((ps_FirstFilter != null) && !ps_FirstFilter.Equals(string.Empty))
                {
                    str = "SELECT * FROM " + ps_TableName + " WHERE " + ps_FieldName + " = N'" + ps_FieldValue.Replace("'", "''") + "' AND (" + ps_FirstFilter + ")";
                }
                else
                {
                    str = "SELECT * FROM " + ps_TableName + " WHERE " + ps_FieldName + " = N'" + ps_FieldValue.Replace("'", "''") + "'";
                }
                command.CommandText = str;
                command.CommandType = CommandType.Text;
                command.Connection = eZConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                CloseConnection(eZConnection);
                if (dataTable.Rows.Count > 0)
                {
                    return dataTable.Rows[0];
                }
                row = null;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            return row;
        }

        public static DataTable SelectTable(string ps_SQLCommand)
        {
            DataTable table2;
            SqlConnection eZConnection = AppConnection;
            DataTable dataTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = ps_SQLCommand;
                command.CommandType = CommandType.Text;
                command.Connection = eZConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                CloseConnection(eZConnection);
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: " + ps_SQLCommand, exception);
            }
            return table2;
        }

        public static void SetBuffer(string ps_VarName, string ps_VarValue)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_ID_String", ps_VarName), new SqlParameter("p_Var_Value", ps_VarValue) };
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Buffer_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void SetMemvar(string ps_VarName, string ps_VarValue)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Var_Name", ps_VarName), new SqlParameter("p_Var_Value", ps_VarValue) };
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Memvar_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void SetMemvar(string ps_VarName, string ps_VarValue, string ps_VarValueE)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Var_Name", ps_VarName), new SqlParameter("p_Var_Value", ps_VarValue), new SqlParameter("p_Var_Value_E", ps_VarValueE) };
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Memvar_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void SetPicture(string ps_TableName, string ps_KeyFieldName, object po_KeyData, string ps_FieldPictName, string ps_FieldPictData, string ps_PicturePath)
        {
            string str;
            SqlParameter[] parameterArray;
            string fileName = null;
            byte[] buffer = null;
            if (!ps_PicturePath.Equals(string.Empty))
            {
                str = "UPDATE " + ps_TableName + " SET " + ps_FieldPictName + " = @p_PictureName, " + ps_FieldPictData + " = @p_PictureData WHERE " + ps_KeyFieldName + " = @p_KeyCode";
                fileName = Path.GetFileName(ps_PicturePath);
                buffer = File.ReadAllBytes(ps_PicturePath);
                parameterArray = new SqlParameter[] { new SqlParameter("p_PictureName", fileName), new SqlParameter("p_PictureData", buffer), new SqlParameter("p_KeyCode", po_KeyData) };
            }
            else
            {
                str = "UPDATE " + ps_TableName + " SET " + ps_FieldPictName + " = null, " + ps_FieldPictData + " = null WHERE " + ps_KeyFieldName + " = @p_KeyCode";
                parameterArray = new SqlParameter[] { new SqlParameter("p_KeyCode", po_KeyData) };
            }
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = str;
                command.CommandType = CommandType.Text;
                command.Connection = eZConnection;
                command.Parameters.AddRange(parameterArray);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static void SetUservar(string ps_UserName, string ps_VarName, string ps_VarValue)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", ps_UserName), new SqlParameter("p_Var_Name", ps_VarName), new SqlParameter("p_Var_Value", ps_VarValue) };
            SqlConnection eZConnection = AppConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "UserVars_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = eZConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                CloseConnection(eZConnection);
            }
            catch (Exception exception)
            {
                throw new NotSupportedException("Invalid expression: ", exception);
            }
        }

        public static bool CheckConnected
        {
            get
            {
                if (!ib_Connected)
                {
                    CloseConnection(AppConnection);
                }
                return ib_Connected;
            }
            set
            {
                ib_Connected = value;
            }
        }

        public static string ConnectionString
        {
            get
            {
                return is_ConnectionString;
            }
            set
            {
                is_ConnectionString = value;
            }
        }

        public static SqlConnection AppConnection
        {
            get
            {
                if (oAppConnection == null)
                {
                    oAppConnection = CreateConnection();
                }
                if ((oAppConnection != null) && (oAppConnection.State == ConnectionState.Closed))
                {
                    oAppConnection.Open();
                }
                return oAppConnection;
            }
            set
            {
                oAppConnection = value;
            }
        }

        #region Tao moi co so du lieu
        /// <summary>
        /// Tạo dữ liệu demo
        /// </summary>
        /// <returns></returns>
        public static bool CreateDEMOData()
        {
            if (!File.Exists(Application.StartupPath + @"\Microsoftdm.dll"))
            {
                return false;
            }
            string sql = AppSecurity.Base64Decode(File.ReadAllText(Application.StartupPath + @"\Microsoftstp.dll"));
            //string sql = File.ReadAllText(Application.StartupPath + @"\DEMODATA.sql");
            AppDataAccess.RunSQLCommand(sql);
            //File.WriteAllText("Microsoftdm1.dll", AppSecurity.Base64Encode(sql));
            return true;
        }

        /// <summary>
        /// Tạo thủ tục cho hệ thống
        /// </summary>
        /// <returns></returns>
        public static bool CreateSTPGs()
        {
            if (!File.Exists(Application.StartupPath + @"\Microsoftstp.dll"))
            {
                return false;
            }
            DataTable table = new DataTable("STPGsFile");
            DataColumn column = null;
            DataRow row = null;
            column = new DataColumn();
            column.ColumnName = "STPGData";
            column.DataType = System.Type.GetType("System.String");
            table.Columns.Add(column);
            column = new DataColumn();
            column.ColumnName = "Passed";
            column.DataType = System.Type.GetType("System.Boolean");
            table.Columns.Add(column);
            string str = AppSecurity.Base64Decode(File.ReadAllText(Application.StartupPath + @"\Microsoftstp.dll"));
            //string str = File.ReadAllText(Application.StartupPath + @"\STPGFILE.sql");
            //File.AppendAllText("Microsoftstp1.dll", AppSecurity.Base64Encode(str));
            string str2 = "\r\n";
            string str3 = "";
            while (str.Contains("begin procedure:->"))
            {
                str3 = str.Substring(0, str.IndexOf("end procedure"));
                str3.Substring(str3.IndexOf("begin procedure:->") + 0x12, str3.IndexOf(str2 + "begin contend:->") - 0x12);
                str3 = str3.Substring(str3.IndexOf("begin contend:->") + 0x10);
                str = str.Substring(str.IndexOf("end procedure") + 13);
                if (!AppDataAccess.RunSQLCommand(str3))
                {
                    row = table.NewRow();
                    row["STPGData"] = str3;
                    row["Passed"] = false;
                    table.Rows.Add(row);
                }
            }

            //if (table.Rows.Count > 0)
            //{
            //    bool flag = true;
            //    while (flag)
            //    {
            //        flag = false;
            //        for (int i = 0; i < table.Rows.Count; i++)
            //        {
            //            row = table.Rows[i];
            //            if (!((bool) row["Passed"]))
            //            {
            //                string sql = row["STPGData"].ToString();
            //                if (!AppDataAccess.RunSQLCommand(sql))
            //                {
            //                    flag = true;
            //                }
            //                else
            //                {
            //                    row["Passed"] = true;
            //                }
            //            }
            //        }
            //    }
            //}
            return true;
        }

        /// <summary>
        /// Tạo dữ liệu hệ thống
        /// </summary>
        /// <returns></returns>
        public static bool CreateSystemData()
        {
            if (!File.Exists(Application.StartupPath + @"\Microsoftsys.dll"))
            {
                return false;
            }
            string str = AppSecurity.Base64Decode(File.ReadAllText(Application.StartupPath + @"\Microsoftsys.dll"));
            //string str = File.ReadAllText(Application.StartupPath + @"\SYSDATA.sql");
            //File.AppendAllText("Microsoftsys1.dll", AppSecurity.Base64Encode(str));
            AppDataAccess.RunSQLCommand(str);
            return true;
        }

        /// <summary>
        /// Tạo các bảng dữ liệu
        /// </summary>
        /// <returns></returns>
        public static bool CreateTables()
        {
            if (!File.Exists(Application.StartupPath + @"\Microsoftdb.dll"))
            {
                return false;
            }
            string str = AppSecurity.Base64Decode(File.ReadAllText(Application.StartupPath + @"\Microsoftdb.dll"));
            //string str = File.ReadAllText(Application.StartupPath + @"\DATABASE.sql");
            //File.AppendAllText("Microsoftdb1.dll", AppSecurity.Base64Encode(str));
            string str2 = "\r\n";
            string str3 = "";
            while (str.Contains(str2 + "go" + str2))
            {
                str3 = str.Substring(0, str.IndexOf(str2 + "go" + str2));
                str = str.Substring(str.IndexOf(str2 + "go" + str2) + 6);
                AppDataAccess.RunSQLCommand(str3);
            }
            return true;
        }
        #endregion

        public static DataTable CheckTrueVer()
        {
            if (!System.IO.File.Exists(Application.StartupPath + @"\license.xml"))
            {
                return null;
            }
            DataTable table = new DataTable("CompanyInfo");
            DataColumn column = new DataColumn();
            column.ColumnName = "LocalName";
            column.DataType = System.Type.GetType("System.String");
            column.MaxLength = 128;
            DataColumn column2 = new DataColumn();
            column2.ColumnName = "EnglishName";
            column2.DataType = System.Type.GetType("System.String");
            column2.MaxLength = 128;
            DataColumn column3 = new DataColumn();
            column3.ColumnName = "LocalAddress";
            column3.DataType = System.Type.GetType("System.String");
            column3.MaxLength = 128;
            DataColumn column4 = new DataColumn();
            column4.ColumnName = "EnglishAddress";
            column4.DataType = System.Type.GetType("System.String");
            column4.MaxLength = 128;
            DataColumn column5 = new DataColumn();
            column5.ColumnName = "SoftwareSerialKey";
            column5.DataType = System.Type.GetType("System.String");
            column5.MaxLength = 128;
            table.Columns.Add(column);
            table.Columns.Add(column2);
            table.Columns.Add(column3);
            table.Columns.Add(column4);
            table.Columns.Add(column5);
            try
            {
                table.ReadXml(Application.StartupPath + @"\license.xml");
                if (table.Rows.Count == 0)
                {
                    return null;
                }
                return table;
            }
            catch
            {
                return null;
            }
        }


        public static bool Register(string fileName)
        {
            if (!File.Exists(fileName))
            {
                MessageBox.Show("Tệp đăng ký không tồn tại!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                return false;
            }
            DataTable table = new DataTable("CompanyInfo");
            DataColumn column = new DataColumn();
            column.ColumnName = "LocalName";
            column.DataType = System.Type.GetType("System.String");
            column.MaxLength = 128;
            DataColumn column2 = new DataColumn();
            column2.ColumnName = "EnglishName";
            column2.DataType = System.Type.GetType("System.String");
            column2.MaxLength = 128;
            DataColumn column3 = new DataColumn();
            column3.ColumnName = "LocalAddress";
            column3.DataType = System.Type.GetType("System.String");
            column3.MaxLength = 128;
            DataColumn column4 = new DataColumn();
            column4.ColumnName = "EnglishAddress";
            column4.DataType = System.Type.GetType("System.String");
            column4.MaxLength = 128;
            DataColumn column5 = new DataColumn();
            column5.ColumnName = "SoftwareSerialKey";
            column5.DataType = System.Type.GetType("System.String");
            column5.MaxLength = 128;
            table.Columns.Add(column);
            table.Columns.Add(column2);
            table.Columns.Add(column3);
            table.Columns.Add(column4);
            table.Columns.Add(column5);
            try
            {
                table.ReadXml(fileName);
                if (table.Rows.Count == 0)
                {
                    MessageBox.Show("Tệp đăng ký không đúng!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                    return false;
                }
                string str = AppSecurity.Base64Decode((string)table.Rows[0]["SoftwareSerialKey"]);
                string str2 = str.Substring(0, 4);
                string str3 = str.Substring(5, 4);
                string str4 = str.Substring(10, 4);
                string str5 = str.Substring(15, 4);
                string str6 = str.Substring(20, 4);
                if (!AppSecurity.SoftwareDecode(0x8b, str2, str3, str4, str5, str6))
                {
                    MessageBox.Show("Tệp đăng ký không đúng!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                    return false;
                }
                string contents = File.ReadAllText(fileName);
                File.WriteAllText(Application.StartupPath + @"\license.xml", contents);
                //AppInstance.CompanyRegister();
                //MessageBox.Show("Đã đăng ký thành công!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}

